#pip install pandas -i  https://pypi.tuna.tsinghua.edu.cn/simple some-package
#pip install sqlalchemy -i  https://pypi.tuna.tsinghua.edu.cn/simple some-package

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy import Table, MetaData, Column, Integer, String
from sqlalchemy.orm import  sessionmaker
from db.utils.dfutils import objfdic,load_json_array_to_object
import json
from db.dao.player import Player
from db.dao.coach import Coach


class MyDBConnection():  # hs.BaseHTTPRequestHandler
    @classmethod    #类的方法 需要使用@classmethod 声明，cls做站位的参数，表示当前类
    def __init__(self):
        # 准备数据库链接对象
        self.host = '192.168.133.4'
        self.user = 'root'
        self.port = '3306'
        self.db = 'self_student_jobs'
        self.password = '123456'
        self.engine = create_engine(f'mysql+pymysql://{self.user}:{self.password}@{self.host}:{self.port}/{self.db}?charset=utf8')
        # cursor = self.conn.cursor()# 如果想要自动建表的话把if_exists的值换为replace, 建议自己建表
        self.Session_class = sessionmaker(bind=self.engine,expire_on_commit=False)  # 实例和engine绑定

    def save_db(self,tb_name,df):
        self.tb_name = tb_name
        # 创建一个连接对象，打开数据库连接
        #self.conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='myspark', charset='utf8')
        # 获取游标
        #
        df.to_sql(self.tb_name, self.engine, if_exists='replace', index=False)

    def insert_entity(self,obj):
        Session = self.Session_class()  # 生成session实例，相当于游标
        Session.add(obj)  # 把要创建的数据对象添加到这个session里， 一会统一创建
        Session.commit()  # 现此才统一提交，创建数据

    def fetch_data(self,tb_name):
          sql = f'select * from {tb_name}'
          df = pd.read_sql(sql,self.engine)
          #print(df)
          return df
    def fetch_data_by_sql(self,sql):
          df = pd.read_sql(sql,self.engine)
          #print(df)
          return json.loads(df.to_json(orient='records'))
    def close(self):
        self.engine.dispose()


    def get_session(self):
        Session = sessionmaker(self.engine)
        return Session()

if __name__ == '__main__':
    coach_list = [[ '0001','0001', '索斯盖特','男'], [ '0002','0001', '瓜迪奥拉','男'],[ '0003','0001', 'aaa','男']]
    df = pd.DataFrame(coach_list, columns=[ 'c_id', 't_id', 'c_name', 'c_sex'])
    myDBConnection = MyDBConnection()
    # print(df)
    # print("--------------------------------")
    # print(dict(df))
    #pandas df 转 list
    #coach_dict_list = df.to_dict('records')
    #pandas df 转json
    print(json.loads(df.to_json(orient='records'))) #to_json 转成json字符串，不能访问属性，所以需要loads 加载成json数组
    coach_array_json = json.loads(df.to_json(orient='records'))
    print(coach_array_json) #有乱码
   #coach_col_list = { 'c_id':'0001','t_id':'0001', 't_name':'索斯盖特','t_sex':'男'}
    #obj = objfdic(coach_col_list)
    # coach = Coach(coach_list,'0001','张三','男')
    coach_obj_list = load_json_array_to_object(coach_array_json,Coach())
    for coach in coach_obj_list:
        myDBConnection.insert_entity(coach)


